﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb; // API for accessing Microsoft Access database

/// <summary>
/// This namespace provide API for accessing main database (dbCNA.mdb)
/// </summary>
namespace DatabaseHelper
{
    /// <summary>
    /// DBHelper Class provides methods for accessing main database (dbCNA.mdb)
    /// </summary>
    public class DBHelper
    {
        #region PROPERTIES
        /// <summary>
        /// Location of database file
        /// </summary>
        private string _filePath;
        /// <summary>
        /// SQL query string
        /// </summary>
        public string Query { get; set; }
        /// <summary>
        /// Location of database file
        /// </summary>
        public string FilePath 
        { 
            get { return _filePath; } 
            set 
            { 
                _filePath = value;
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + value; 
            } 
        }

        /// <summary>
        /// Open connection to database
        /// </summary>
        private OleDbConnection connection;
        /// <summary>
        /// SQL query or procedure to execute 
        /// </summary>
        private OleDbCommand command;
        /// <summary>
        /// Bridge use to update database and fill dataset
        /// </summary>
        private OleDbDataAdapter dtAdapter;
        /// <summary>
        /// Automatically generate command
        /// </summary>
        private OleDbCommandBuilder cmdBuilder;
        /// <summary>
        /// Connection string use to open database file
        /// </summary>
        private string strConnection;
        #endregion 

        #region CONSTRUCTORS
        /// <summary>
        /// Default constructor
        /// <remarks>
        /// String connection will be assigned to default database file location.
        /// </remarks>
        /// </summary>
        public DBHelper()
        {
            connection = new OleDbConnection();
            command = new OleDbCommand();
            dtAdapter = new OleDbDataAdapter();
            cmdBuilder = new OleDbCommandBuilder();
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbCNA.mdb";
        }

        /// <summary>
        /// Constructor with database file path
        /// </summary>
        /// <param name="filePath">File path of database</param>
        public DBHelper(string filePath)
            : this()
        {
            FilePath = filePath;
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath;
        }

        /// <summary>
        /// Constructor with query and database file path
        /// </summary>
        /// <param name="query">Query string</param>
        /// <param name="filePath">File path of database</param>
        public DBHelper(string query, string filePath)
            : this(filePath)
        {
            Query = query;
        }
        #endregion

        #region DIS/CONNECT EXECUTE
        /// <summary>
        /// Open connection to database
        /// </summary>
        private void ConnectDB()
        {
            try
            {
                connection = new OleDbConnection(strConnection);
                connection.Open();
                command.CommandText = Query;
                command.Connection = connection;
                dtAdapter = new OleDbDataAdapter(command);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        /// <summary>
        /// Close connection to database
        /// </summary>
        private void DisconnectDB()
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }

        /// <summary>
        /// Execute the query
        /// </summary>
        public void Execute()
        {
            try
            {
                ConnectDB();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            DisconnectDB();
        }

        /// <summary>
        /// Execute the query
        /// </summary>
        public int ExecuteAddCommand()
        {
            int id = 0;
            try
            {
                ConnectDB();
                command.ExecuteNonQuery();
                command.CommandText = "SELECT @@Identity";
                id = (int)command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            DisconnectDB();
            return id;
        }
        #endregion
        
        #region FILL
        /// <summary>
        /// Fill a DataTable using SELECT query
        /// </summary>
        /// <returns>Return null if fail. Return selected DataTable if success</returns>
        public DataTable Fill()
        {
            DataTable dt = new DataTable();
            try
            {
                ConnectDB();
                dtAdapter.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            DisconnectDB();
            return dt;
        }
        #endregion

        #region UPDATE
        /// <summary>
        /// Update database with DataTable using SELECT 
        /// </summary>
        /// <param name="dtTable"></param>
        public void Update(DataTable dtTable)
        {
            try
            {
                dtAdapter = new OleDbDataAdapter(Query, strConnection);
                cmdBuilder = new OleDbCommandBuilder(dtAdapter);
                dtAdapter.Update(dtTable);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            DisconnectDB();
        }
        public int Update(int id, PRecord record, string pipID)
        {
            int lastId;
            Query = "UPDATE Records SET " + 
                "PipelineID = '" + pipID + "'," + 
                "TimeStart = '" + record.TimeStart + "'," +
                "TimeEnd = '" + record.TimeEnd + "'," +
                "KPStart = '" + record.KPStart + "'," +
                "KPEnd = '" + record.KPEnd + "'," +
                "Video1 = '" + record.Video1 + "'," +
                "Video2 = '" + record.Video2 + "'," +
                "Video3 = '" + record.Video3 + "'," +
                "Video4 = '" + record.Video4 + "'" + 
                "WHERE RecordID = " + id;
            lastId = ExecuteAddCommand();
            return lastId;
        }

        public void UpdateProjectLocation(int id, string location)
        {
            DataTable dtRecords = SelectProjectRecords(id);
            PProject ppro = new PProject();
            ppro = FindProject(id);
            string projectName = ppro.ProjectName;
            foreach (DataRow dr in dtRecords.Rows)
            {
                int recordID = int.Parse(dr["RecordID"].ToString());
                string pipelineName = FindPipelineName(recordID);

                string path = location + projectName + "\\" + pipelineName + "\\Record#" + recordID.ToString() + "_v";
                string newVideo1Path = path + "1.avi";
                string newVideo2Path = path + "2.avi";
                string newVideo3Path = path + "3.avi";
                string newVideo4Path = path + "4.avi";

                Query = "UPDATE Records SET " +
                    "Video1 = '" + newVideo1Path + "'," +
                    "Video2 = '" + newVideo2Path + "'," +
                    "Video3 = '" + newVideo3Path + "'," +
                    "Video4 = '" + newVideo4Path + "'" +
                    "WHERE RecordID = " + recordID;
                Execute();

            }
        }

        public DataTable SelectProjectEvents(int id)
        {
            DataTable dtPipelines = new DataTable();
            DataTable dtRecords = new DataTable();
            DataTable dtEvents = new DataTable();
            
            Query = "SELECT * FROM Pipelines WHERE ProjectID = " + id;
            dtPipelines = Fill();

            if (dtPipelines.Rows.Count > 0)
            {
                string sqlProjectRecords = "SELECT * FROM Records WHERE PipelineID IN (";
                foreach (DataRow dr in dtPipelines.Rows)
                {
                    int pipelineID = int.Parse(dr["PipelineID"].ToString());
                    sqlProjectRecords += pipelineID.ToString() + ",";
                }
                sqlProjectRecords += ")";

                Query = sqlProjectRecords;
                dtRecords = Fill();

                if (dtRecords.Rows.Count > 0)
                {
                    string sqlProjectEvents = "SELECT * FROM Events WHERE RecordID IN (";
                    foreach (DataRow dr in dtRecords.Rows)
                    {
                        int recordID = int.Parse(dr["RecordID"].ToString());
                        sqlProjectEvents += recordID.ToString() + ",";
                    }
                    sqlProjectEvents += ")";

                    Query = sqlProjectEvents;
                    dtEvents = Fill();
                }
            }
            return dtEvents;
        }

        public DataTable SelectProjectRecords(int id)
        {
            DataTable dtPipelines = new DataTable();
            DataTable dtRecords = new DataTable();
            DataTable dtEvents = new DataTable();

            Query = "SELECT * FROM Pipelines WHERE ProjectID = " + id;
            dtPipelines = Fill();

            if (dtPipelines.Rows.Count > 0)
            {
                string sqlProjectRecords = "SELECT * FROM Records WHERE PipelineID IN (";
                foreach (DataRow dr in dtPipelines.Rows)
                {
                    int pipelineID = int.Parse(dr["PipelineID"].ToString());
                    sqlProjectRecords += pipelineID.ToString() + ",";
                }
                sqlProjectRecords += ")";

                Query = sqlProjectRecords;
                dtRecords = Fill();
            }
            return dtRecords;
        }
        #endregion

        #region ADD
        /// <summary>
        /// Add a project to database (Projects table)
        /// </summary>
        /// <param name="project">Input project as PProject class object</param>
        public void Add(PProject project)
        {
            Query = "INSERT INTO Projects(ProjectName,Location) VALUES('" + project.ProjectName + "','" + project.Location + "')";
            Execute();
        }
        /// <summary>
        /// Add a pipeline to database (Pipelines table)
        /// </summary>
        /// <param name="pipeline">Input pipeline as PPipeline class object</param>
        /// <param name="proID">Project ID of input pipeline</param>
        public void Add(PPipeline pipeline, int proID)
        {
            Query = "INSERT INTO Pipelines (ProjectID, PipelineName, PipelineSummary) VALUES ('" +
                proID + "','" +
                pipeline.PipelineName + "','" +
                pipeline.PipelineSummary + 
                "')";
            Execute();
        }
        /// <summary>
        /// Add a record to database (Records table)
        /// </summary>
        /// <param name="record">Input record as PRecord class object</param>
        /// <param name="pipID">Pipeline ID of input record</param>
        public int Add(PRecord record, string pipID)
        {
            int lastId;
            Query = "INSERT INTO Records (PipelineID, TimeStart, TimeEnd, KPStart, KPEnd, Video1, Video2, Video3, Video4) VALUES ('" +
                pipID + "','" +
                record.TimeStart + "','" +
                record.TimeEnd + "','" +
                record.KPStart + "','" +
                record.KPEnd + "','" +
                record.Video1 + "','" +
                record.Video2 + "','" +
                record.Video3 + "','" +
                record.Video4 + 
                "')";
            lastId = ExecuteAddCommand();
            return lastId;
        }
        /// <summary>
        /// Add a event to database (Events table)
        /// </summary>
        /// <param name="evnt">Input event as PEvent class object</param>
        /// <param name="recID">Record ID of input event</param>
        public void Add(PEvent evnt, string recID)
        {
            Query = "INSERT INTO Events (RecordID, EventTime, Description, GeneralComment, Comment, KP, Easting, Northing) VALUES ('" +
                recID + "','" +
                evnt.EventTime + "','" +
                evnt.Description + "','" +
                evnt.GeneralComment + "','" +
                evnt.Comment + "','" +
                evnt.KP + "','" +
                evnt.Easting + "','" +
                evnt.Northing +
                "')";
            Execute();
        }
        /// <summary>
        /// Add a survey data to database (Data table)
        /// </summary>
        /// <param name="data">Input data as PData class object</param>
        /// <param name="recID">Record ID of input data</param>
        public void Add(PData data, string recID)
        {
            Query = "INSERT INTO Data (RecordID, DataTime, KP, Easting, Northing, CP) VALUES ('" +
                recID + "','" +
                data.DataTime + "','" +
                data.KP + "', '" +
                data.Easting + "','" +
                data.Northing + "','" +
                data.CP + 
                "')";
            Execute();
        }
        /// <summary>
        /// Add a list of events to database (Events table)
        /// </summary>
        /// <param name="lstEvent">List of events</param>
        public void Add(List<PEvent> lstEvent)
        {
            // For testing purpose
            // Use to calculate tatol insert time
            //DateTime t_start = DateTime.Now;

            foreach (PEvent evnt in lstEvent)
            {
                Add(evnt,"1");
            }

            // For testing purpose
            // Use to calculate tatol insert time
            /*
            DateTime t_end = DateTime.Now;
            string duration = (t_end - t_start).ToString();
            string msg =
                "Total rows: " + lstEvent.Count.ToString() + "\n" +
                "Total time: " + duration; 
            MessageBox.Show(msg);
            */
        }

        /// <summary>
        /// Add a list of data to database (Data table)
        /// </summary>
        /// <param name="lstData">List of data</param>
        public void Add(List<PData> lstData)
        {
            // For testing purpose
            // Use to calculate tatol insert time
            //DateTime t_start = DateTime.Now;
            DateTime t_start = DateTime.Now;

            // For testing purpose
            string recID = "1";
            try
            {
                connection = new OleDbConnection(strConnection);
                connection.Open();
                command.Connection = connection;
                dtAdapter = new OleDbDataAdapter(command);
                foreach (PData data in lstData)
                {
                    command.CommandText = "INSERT INTO Data(RecordID,DataTime,KP,Easting,Northing,CP) VALUES('" + recID + "','" + data.DataTime + "','" + data.KP + "','" + data.Easting + "','" + data.Northing + "','" + data.CP + "')";
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            DisconnectDB();

            // For testing purpose
            // Use to calculate tatol insert time
            /*
            DateTime t_end = DateTime.Now;
            string duration = (t_end - t_start).ToString();

            string msg =
                "Total rows: " + lstData.Count.ToString() + "\n" +
                "Total time: " + duration;
            MessageBox.Show(msg);
            */
        }
        #endregion

        #region FIND
        /// <summary>
        /// Find a project by ID
        /// </summary>
        /// <param name="proID">ProjectID</param>
        /// <returns>Return a project as PProject class object</returns>
        public PProject FindProject(int proID)
        {
            Query = "SELECT * FROM Projects WHERE ProjectID=" + proID;
            DataTable found = Fill();
            PProject pProject = new PProject();
            if (found.Rows.Count > 0)
            {
                pProject.ProjectName = found.Rows[0]["ProjectName"].ToString();
                pProject.Location = found.Rows[0]["Location"].ToString();
            }
            return pProject;
        }

        public int FindProject(string name)
        {
            Query = "SELECT * FROM Projects WHERE ProjectName='" + name + "'";
            DataTable found = Fill();
            if (found.Rows.Count > 0)
            {
                return int.Parse(found.Rows[0]["ProjectID"].ToString());
            }
            return (int)-1;
        }

        /// <summary>
        /// Find a pipeline by ID
        /// </summary>
        /// <param name="pipID">PipelineID</param>
        /// <returns>Return a pipeline as PPipeline class object</returns>
        public PPipeline FindPipeline(int pipID)
        {
            Query = "SELECT * FROM Pipelines WHERE PipelineID=" + pipID;
            DataTable found = Fill();
            PPipeline pPipeline = new PPipeline();
            if (found.Rows.Count > 0)
            {
                pPipeline.PipelineName = found.Rows[0]["PipelineName"].ToString();
            }
            return pPipeline;
        }
        /// <summary>
        /// Find a record by ID
        /// </summary>
        /// <param name="recID">RecordID</param>
        /// <returns>Return a record as PRecord class object</returns>
        /// 

        public string FindPipelineName(int recId)
        {
            Query = "SELECT * FROM Records WHERE RecordID=" + recId;
            DataTable dt = Fill();
            if (dt.Rows.Count > 0)
            {
                int pipelineId = int.Parse(dt.Rows[0]["PipelineID"].ToString());
                Query = "SELECT * FROM Pipelines WHERE PipelineID=" + pipelineId;
                DataTable found = Fill();
                if (found.Rows.Count > 0)
                {
                    return found.Rows[0]["PipelineName"].ToString();
                }
            }
            return "";
        }

        public PRecord FindRecord(int recID)
        {
            Query = "SELECT * FROM Records WHERE RecordID=" + recID;
            DataTable found = Fill();
            PRecord pRecord = new PRecord();
            if (found.Rows.Count > 0)
            {
                pRecord.TimeStart = DateTime.Parse(found.Rows[0]["TimeStart"].ToString());
                pRecord.TimeEnd = DateTime.Parse(found.Rows[0]["TimeEnd"].ToString());
                pRecord.KPStart = found.Rows[0]["KPStart"].ToString();
                pRecord.KPEnd = found.Rows[0]["KPEnd"].ToString();
                pRecord.Video1 = found.Rows[0]["Video1"].ToString();
                pRecord.Video2 = found.Rows[0]["Video2"].ToString();
                pRecord.Video3 = found.Rows[0]["Video3"].ToString();
                pRecord.Video4 = found.Rows[0]["Video4"].ToString();
            }
            return pRecord;
        }
        /// <summary>
        /// Find a event by ID
        /// </summary>
        /// <param name="evntID">EventID</param>
        /// <returns>Return a event as PEvent class object</returns>
        public PEvent FindEvent(int evntID)
        {
            Query = "SELECT * FROM Events WHERE EventID=" + evntID;
            DataTable found = Fill();
            PEvent pEvent = new PEvent();
            if (found.Rows.Count > 0)
            {
                pEvent.EventTime = DateTime.Parse(found.Rows[0]["EventTime"].ToString());
                pEvent.Description = found.Rows[0]["Description"].ToString();
                pEvent.GeneralComment = found.Rows[0]["GeneralComment"].ToString();
                pEvent.Comment = found.Rows[0]["Comment"].ToString();
                pEvent.KP = found.Rows[0]["KP"].ToString();
                pEvent.Easting = found.Rows[0]["Easting"].ToString();
                pEvent.Northing = found.Rows[0]["Northing"].ToString();
            }
            return pEvent;
        }
        /// <summary>
        /// Find a survey data by ID
        /// </summary>
        /// <param name="dataID">DataID</param>
        /// <returns>A survey data as PData class object</returns>
        public PData FindData(int dataID)
        {
            Query = "SELECT * FROM Data WHERE DataID=" + dataID;
            DataTable found = Fill();
            PData pData = new PData();
            if (found.Rows.Count > 0)
            {
                pData.DataTime = DateTime.Parse(found.Rows[0]["DataTime"].ToString());
                pData.KP = found.Rows[0]["KP"].ToString();
                pData.Easting = found.Rows[0]["Easting"].ToString();
                pData.Northing = found.Rows[0]["Northing"].ToString();
                pData.CP = found.Rows[0]["CP"].ToString();
            }
            return pData;
        }
        /// <summary>
        /// Find a survey data at specific time in the database
        /// </summary>
        /// <param name="time">Time use to find</param>
        /// <param name="s_error">Tolerance time in seconds</param>
        /// <returns>A survey data as PData class object</returns>
        public PData FindData(DateTime time, int s_error)
        {
            Query = "SELECT MIN(ABS(DATEDIFF('s', DataTime, '" + time + "'))) FROM Data";

            DataTable nearestDataTime = Fill();
            int min = int.Parse(nearestDataTime.Rows[0].ItemArray[0].ToString());

            PData pdata = new PData();
            if (min < s_error)
            {
                Query = "SELECT * FROM Data WHERE ABS(DATEDIFF('s', DataTime, '" + time + "'))=" + min;
                DataTable nearestData = Fill();

                pdata.DataTime = DateTime.Parse(nearestData.Rows[0]["DataTime"].ToString());
                pdata.KP = nearestData.Rows[0]["KP"].ToString();
                pdata.Easting = nearestData.Rows[0]["Easting"].ToString();
                pdata.Northing = nearestData.Rows[0]["Northing"].ToString();
                pdata.CP = nearestData.Rows[0]["CP"].ToString();
            }
            else
            {
                pdata.KP = "No data";
                pdata.Easting = "No data";
                pdata.Northing = "No data";
                pdata.CP = "No data";
            }

            return pdata;
        }
        #endregion

        #region EXPORT
        public void ExportAllEventsToExcel(int projectId)
        {
            DataSet ds = new DataSet();
            DataTable dtPipelines = new DataTable();
            try
            {
                ConnectDB();

                dtAdapter.SelectCommand.CommandText = "SELECT * FROM Pipelines WHERE ProjectID = " + projectId;
                dtAdapter.Fill(dtPipelines);

                foreach (DataRow drPipeline in dtPipelines.Rows)
                {
                    DataTable dtRecords = new DataTable();
                    int pipelineId = int.Parse(drPipeline["PipelineID"].ToString());
                    string pipelineName = drPipeline["PipelineName"].ToString();

                    dtAdapter.SelectCommand.CommandText = "SELECT * FROM Records WHERE PipelineID = " + pipelineId;
                    dtAdapter.Fill(dtRecords);
                    string str = "";
                    foreach (DataRow drRecord in dtRecords.Rows)
                    {
                        int recordId = int.Parse(drRecord["RecordID"].ToString());
                        str += recordId.ToString() + ",";
                    }
                    if (str != "")
                    {
                        dtAdapter.SelectCommand.CommandText = "SELECT * FROM Events WHERE RecordID IN (" + str + ")";
                        dtAdapter.Fill(ds, pipelineName);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            DisconnectDB();

            ExportToExcel(ds, @"E:\test.xls");
        }
        
        public void ExportToExcel(DataSet dataSet, string outputPath)
        {
            // Create the Excel Application object
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

            // Create a new Excel Workbook
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

            // see the excel sheet behind the program
            excelApp.Visible = true;

            int sheetIndex = 0;

            try
            {
                // Copy each DataTable
                foreach (System.Data.DataTable dt in dataSet.Tables)
                {
                    // Copy the DataTable to an object array
                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                    // Copy the column names to the first row of the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        rawData[0, col] = dt.Columns[col].ColumnName;
                    }

                    // Copy the values to the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        for (int row = 0; row < dt.Rows.Count; row++)
                        {
                            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                        }
                    }

                    // Calculate the final column letter
                    string finalColLetter = string.Empty;
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                    int colCharsetLen = colCharset.Length;

                    if (dt.Columns.Count > colCharsetLen)
                    {
                        finalColLetter = colCharset.Substring(
                            (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                    }

                    finalColLetter += colCharset.Substring(
                            (dt.Columns.Count - 1) % colCharsetLen, 1);

                    // Create a new Sheet
                    Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Sheets.Add(
                        excelWorkbook.Sheets.get_Item(++sheetIndex),
                        Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

                    excelSheet.Name = dt.TableName;

                    // Fast data export to Excel
                    string excelRange = string.Format("A1:{0}{1}",
                        finalColLetter, dt.Rows.Count + 1);

                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                    // Mark the first row as BOLD
                    ((Microsoft.Office.Interop.Excel.Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            // Collect the unreferenced objects
            GC.Collect();
            GC.WaitForPendingFinalizers();
            
        }

        #endregion

        #region DELETE
        public void DeleteProjectsExcept(int id)
        {
            Query = "SELECT * FROM Projects WHERE ProjectID != " + id;
            DataTable dt = Fill();
            foreach (DataRow dr in dt.Rows)
            {
                int projectID = int.Parse(dr["ProjectID"].ToString());
                DeleteProject(projectID);
            }
        }

        public void DeleteProjectsExcept(string name)
        {
            Query = "SELECT * FROM Projects WHERE ProjectName <> '" + name + "'";
            DataTable dt = Fill();
            foreach (DataRow dr in dt.Rows)
            {
                int projectID = int.Parse(dr["ProjectID"].ToString());
                DeleteProject(projectID);
            }
        }

        public void DeleteProject(int id)
        {
            Query = "SELECT * FROM Pipelines WHERE ProjectID = " + id;
            DataTable dt = Fill();
            foreach (DataRow dr in dt.Rows)
            {
                int pipelineID = int.Parse(dr["PipelineID"].ToString());
                DeletePipeline(pipelineID);
            }
            Query = "DELETE FROM Projects WHERE ProjectID = " + id;
            Execute();
        }

        public void DeletePipeline(int id)
        {
            Query = "SELECT * FROM Records WHERE PipelineID = " + id;
            DataTable dt = Fill();
            foreach (DataRow dr in dt.Rows)
            {
                int recordID = int.Parse(dr["RecordID"].ToString());
                DeleteRecord(recordID);
            }
            Query = "DELETE FROM Pipelines WHERE PipelineID = " + id;
            Execute();
        }

        public void DeleteRecord(int id)
        {
            Query = "SELECT * FROM Events WHERE RecordID = " + id;
            DataTable dt = Fill();
            string deleteQuery = "DELETE FROM Events WHERE EventID IN (";
            foreach (DataRow dr in dt.Rows)
            {
                int eventID = int.Parse(dr["EventID"].ToString());
                deleteQuery += eventID.ToString() + ",";
            }
            deleteQuery += ")";
            Query = deleteQuery;
            Execute();
            Query = "DELETE FROM Records WHERE RecordID = " + id;
            Execute();
        } 
        #endregion
    }
}
